记一次水平分表

前言

最近项目中的一个rds的磁盘频频告警，查看后发现有些表的数据量巨大，其中有三个关联表，部分表的大小已达到80+G。跟产品确认后，决定只保留近一段时间内的数据。那必然需要加上一个时间的索引，但是表数据量已到达几个亿，直接在原表上根据时间去创建索引显然是不可行的，所以第一步先考虑水平拆表。

拆表策略

常用的拆表策略有两种。

Range
根据时间去拆表，或者id去拆表，如: id <= 100000 -> data_1, 100000 < id <= 200000 -> data_2...，一般日志表用的较多。

Hash
像HashMap一样，采用Hash+mod的方式去获取下标。Hash是为了防止下标重复并且能够尽量的均匀。

因为我们的业务是和用户相关的，不好按照日期这些去分表，同时业务表是基于用户的Id去获取下标的，本身就是唯一的，所以直接mod了。

具体操作

旧表是分为5个，重新拆表为128个，预计重新分表后每个表的数据量在200W-400W之间。因为我们的数据是依赖第三方的数据，可以直接通过接口去获取指定时间内的数据，所以不需要考虑处理旧数据时产生的新数据。（可能会奇怪为什么通过接口就能获取到指定时间的数据，为什么本地还需要存储？一个是调用接口需要付费，如果每次都通过接口去获取，额外需要比较多的成本，第二个则是有其他功能依赖于这个数据）如果考虑到新数据，可以在原有的接口上加上对新表的操作，在处理旧数据时根据唯一键，或者一些判断去判断是否为同一数据处理。

旧的数据如何处理？直接开了四个线程把旧数据重新分表后拆入到新表(因为不是停机维护，所以旧表的数据并没有删除)，过程比较简单，就不叙述了。

迁移完成后，先上线了用户重新分表后的逻辑，以保障用户的后续操作能够全部落在新表，至此旧表的无用数据不再增加。

全部完成后，观察一段时间，线上无用户反馈，那么可以开始清理旧表的无用数据了。令人头疼的事开始了，旧表内有大量的无效数据，但同时仍然有部分用户再分表后还是落在这5个表内。一开始我选择了遍历所有用户，把再分表后不是落在这5个表的用户的数据删除。

delete from table_name WHERE user_id= #userId# limit 2000;

然而看数据库监控的时候发现，服务器资源的IOPS飙到了8000(正常是在3000左右)，我们的配置是在6000，所以已经是会影响到线上用户的正常使用。后来改为执行一次SQL就Thread.sleep(500)，IOPS是不高了，但是删除的速度也是特别的慢。而且会产生一个问题:要知道delete是不会释放删除的数据的空间的，我们需要手动的去释放这些空间。然而去释放几十个G的空间是非常慢的，这也会严重的影响用户的使用。

咨询了别人后，得到了一个解决方案。

重新创建5个临时表，把再分表后仍然落入旧表的用户数据迁入临时表，完成后直接rename临时表为旧表名，再把旧表删除就可以了。rename的效率是非常快的，几百毫秒即可。这个方案不仅大大节省了删除无效数据的时间，而且也不需要手动去释放旧表空间。

至此，这次分表就结束啦。

Tips

为什么要分为128张表？
一般建议，分表大小要为2^n,主要是有两个好处:
1.mod的时候可以使用 & 代替 %，位运算的效率要高
2.下次再分表的时候，可以保证尽可能少的数据会被迁移

统计数据库大小:

SELECT TABLE_SCHEMA,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 FROM information_schema.TABLES group by TABLE_SCHEMA ORDER BY (sum(DATA_LENGTH)+sum(INDEX_LENGTH)) DESC;

统计一个数据库各个表大小:

select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as table_size, table_name as data from information_schema.TABLES  where table_schema='TABLE_NAME' group by table_name;

释放表空间:
optimize table table_name;

重命名表:
rename table table_name;